export default function (sqlite) {
  return {
    // 获取分红列表
    getDividendList(
      pageSize,
      offset,
      startDate = null,
      endDate = null,
      dealerName = null,
      dealerLevelId = null
    ) {
      return new Promise((resolve, reject) => {
        // 基础查询条件
        let baseSql =
          'SELECT a.parent_dealer_id,a.parent_dealer_name,a.dealer_name,a.dealer_id,a.dealer_level_id,a.dealer_level_name FROM dealer AS a LEFT JOIN performance AS b ON a.dealer_id=b.dealer_id WHERE 1=1'
        let paramSql = ''
        let params = []
        if (startDate) {
          paramSql += ' AND b.performance_date >= ?'
          params.push(startDate)
        }
        if (endDate) {
          paramSql += ' AND b.performance_date <= ?'
          params.push(endDate)
        }

        // 添加 dealer_name 模糊查询条件（如果提供）
        if (dealerName) {
          paramSql += ' AND a.dealer_name LIKE ?'
          params.push(`%${dealerName}%`)
        }

        // 添加 dealer_level_id 精确查询条件（如果提供）
        if (dealerLevelId) {
          paramSql += ' AND a.dealer_level_id = ?'
          params.push(dealerLevelId)
        }

        // 构建获取总数的查询
        let countSql = `SELECT COUNT(*) AS total FROM dealer AS a LEFT JOIN performance AS b ON a.dealer_id=b.dealer_id WHERE 1=1`
        countSql += paramSql

        // 构建分页查询
        let dataSql = baseSql + paramSql
        dataSql += ` ORDER BY b.create_time DESC LIMIT ? OFFSET ?`

        const dataParams = [...params, pageSize, offset] // 分页参数

        // 执行两个查询：一个统计总数，一个获取分页数据
        sqlite.get(countSql, params, (err, countRow) => {
          if (err) {
            console.error('Error counting getDividendList records:', err.message)
            reject(err.message)
          } else {
            const total = countRow.total

            // 查询实际数据
            sqlite.all(dataSql, dataParams, (err, rows) => {
              if (err) {
                console.error('Error fetching getDividendList data:', err.message)
                reject(err.message)
              } else {
                resolve({ total, rows })
              }
            })
          }
        })
      })
    },
    // 获取经销商团队业绩
    getDealerPerformance(dealerId = null, startDate = null, endDate = null) {
      return new Promise((resolve, reject) => {
        let dataSql = `WITH RECURSIVE subordinates(dealer_id) AS (
    SELECT dealer_id
    FROM dealer
    WHERE dealer_id = ?
 
    UNION ALL
 
    SELECT a.dealer_id
    FROM dealer a
    INNER JOIN subordinates b ON a.parent_dealer_id = b.dealer_id
)
SELECT SUM(n.performance_money) AS money
FROM subordinates AS m
INNER JOIN performance AS n ON m.dealer_id=n.dealer_id
WHERE m.dealer_id != ? `

        const params = []
        params.push(dealerId)
        params.push(dealerId)

        if (startDate) {
          dataSql += ' AND n.performance_date >= ?'
          params.push(startDate)
        }

        if (endDate) {
          dataSql += ' AND n.performance_date <= ?'
          params.push(endDate)
        }
        sqlite.get(dataSql, params, (err, rows) => {
          if (err) {
            console.error('Error counting getDealerPerformance records:', err.message)
            reject(err.message)
          } else {
            resolve({ rows })
          }
        })
      })
    },

    // 获取经销商团队业绩-不包含经销商自己
    getDealerTeamPerformance(dealerId = null, performanceDate = null) {
      return new Promise((resolve, reject) => {
        let dataSql = `WITH RECURSIVE subordinates(dealer_id) AS (
    SELECT dealer_id
    FROM dealer
    WHERE dealer_id = ?
 
    UNION ALL
 
    SELECT a.dealer_id
    FROM dealer a
    INNER JOIN subordinates b ON a.parent_dealer_id = b.dealer_id
)
SELECT SUM(n.performance_money) AS money
FROM subordinates AS m
INNER JOIN performance AS n ON m.dealer_id=n.dealer_id
WHERE m.dealer_id != ? AND n.performance_date=?`

        const params = []
        params.push(dealerId)
        params.push(dealerId)
        params.push(performanceDate)
        sqlite.get(dataSql, params, (err, rows) => {
          if (err) {
            console.error('Error counting getDealerTeamPerformance records:', err.message)
            reject(err.message)
          } else {
            resolve({ rows })
          }
        })
      })
    },

    // 获取经销商个人业绩
    getDealerPersonPerformance(dealerId = null, startDate = null, endDate = null) {
      return new Promise((resolve, reject) => {
        let dataSql = `
            SELECT SUM(a.performance_money) AS money
            FROM performance AS a
            WHERE a.dealer_id = ? `

        const params = []
        params.push(dealerId)

        if (startDate) {
          dataSql += ' AND a.performance_date >= ?'
          params.push(startDate)
        }

        if (endDate) {
          dataSql += ' AND a.performance_date <= ?'
          params.push(endDate)
        }
        sqlite.get(dataSql, params, (err, rows) => {
          if (err) {
            console.error('Error counting getDividendList records:', err.message)
            reject(err.message)
          } else {
            resolve({ rows })
          }
        })
      })
    }
  }
}
